/**
 * 
 */
package monitTree;

import com.opensymphony.xwork2.ActionContext;
import db_tool.DBConnection;
import org.apache.struts2.ServletActionContext;
import tree_util.Account;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *类描述：
 *@author: blank
 *@date： 日期：2017年4月7日 时间：下午4:53:51
 *@version 1.0
 */
public class Search {

	private String River;
	private String Site;
	private String InvestigationDate;
	
	public String getRiver() {
		return River;
	}

	public void setRiver(String river) {
		River = river;
	}

	public String getSite() {
		return Site;
	}

	public void setSite(String site) {
		Site = site;
	}

	public String getInvestigationDate() {
		return InvestigationDate;
	}

	public void setInvestigationDate(String investigationDate) {
		InvestigationDate = investigationDate;
	}

	private Account account = (Account) ActionContext.getContext().getSession().get("account");
	
	public String execute() throws IOException{
		System.out.println("水域："+River);
		System.out.println("地点："+Site);
		System.out.println("日期："+InvestigationDate);
		ServletActionContext.getResponse().setContentType("text/html;charset=utf-8");
		PrintWriter writer = ServletActionContext.getResponse().getWriter();
		String result = "";
		DBConnection db_connection = DBConnection.getInstance();
		
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = null;
		
		if(account.getRoleName().equals("student")){
			sql = searchSql(River,Site,InvestigationDate) + "and user_id='" + account.getID() + "'";
		}else if(account.getRoleName().equals("mentor")) {
			sql = searchSql(River,Site,InvestigationDate) + "and user_id IN ( SELECT id FROM Account WHERE superiorName ='"+account.getUsername()+"') OR user_id = '"+account.getID() + "'";
		}			
		else if(account.getRoleName().equals("privilege")){
			sql = searchSql(River,Site,InvestigationDate);
		}
		System.out.println("搜索语句："+sql);
		try {
			statement = db_connection.getStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			resultSet = statement.executeQuery(sql);
			while(resultSet.next()){
				result+=resultSet.getString(1)+",";
			}
			if(result.length()==0){writer.write("none");}
			else{
				result = result.substring(0, result.length()-1);
			 	writer.write(result);
			}
			System.out.println("monitSite ID result:"+result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			writer.write("error");
		} finally{
			try {
				db_connection.close(resultSet,statement);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	public String searchSql(String River,String Site,String InvestigationDate) throws IOException{
		if(River.equals("")) {River = "%";}
		if(Site.equals("")) {Site = "%";}
		if(InvestigationDate.equals("")) {InvestigationDate = "%";}
		return "SELECT InverstigationID FROM MonitoringSite where River like '%"+River+"%' and Site like '%"+Site+"%' and convert(varchar(50),InvestigationDate,120) like '%"+InvestigationDate+"%'";
	}
}
